package com.teach.dao;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.sun.org.apache.bcel.internal.generic.NEW;
import com.teach.commons.JDBCUtil;
import com.teach.entity.Lj;
import com.teach.entity.User;
/**
 * <pre>
 * 数据访问对象
 * 文章
 * TableName : ARTICLE
 * ClassName : com.teach.dao.ArticleDAO
 * Author : J.L.Zhou
 * E-Mail : 2233875735@qq.com
 * Date : 2020-07-02 11:20:40
 * Version : 1.0
 * Copyright 2020 jlzhou.top Inc. All rights reserved. 
 * Warning: this content is only for internal circulation of the company. It is forbidden to divulge it or use it for other commercial purposes
 * </pre>
 **/
public class LjDAO implements DAOSupport<Lj>{

	@Override
	public int insert(Lj entity) throws SQLException {
		String sql = "insert into lj (LJ_ID,LJ_NAME,LJ_NICKNAME,LJ_PASSWORD,LJ_SALT,LJ_PHONE,LJ_QQ,LJ_SEX,LJ_BRITHDATE,LJ_ADDRESS_CODE,LJ_ADDRESS,LJ_LOVES,LJ_STATUS) value(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try(
				
				Connection con = JDBCUtil.getConnection();
				PreparedStatement pstmt = con.prepareStatement(sql);
		){
			pstmt.setObject(1, JDBCUtil.nextId());
			pstmt.setObject(2, entity.getLjName());
			pstmt.setObject(3, entity.getLjNickname());
			pstmt.setObject(4, entity.getLjPassword());
			pstmt.setObject(5, entity.getLjSalt());
			pstmt.setObject(6, entity.getLjPhone());
			pstmt.setObject(7, entity.getLjQq());
			pstmt.setObject(8, entity.getLjSex());
			pstmt.setObject(9, entity.getLjBrithdate());
			pstmt.setObject(10, entity.getLjAddressCode());
			pstmt.setObject(11, entity.getLjAddress());
			pstmt.setObject(12, entity.getLjLoves());
			pstmt.setObject(13, entity.getLjStatus());
			
			return pstmt.executeUpdate();
		}
	}

	@Override
	public int updateById(Lj entity) throws SQLException {
		String sql = "update lj set(LJ_ID=?,LJ_NAME=?,LJ_NICKNAME=?,LJ_PASSWORD=?,LJ_SALT=?,LJ_PHONE=?,LJ_QQ=?,LJ_SEX=?,LJ_BRITHDATE=?,LJ_ADDRESS_CODE=?,LJ_ADDRESS=?,LJ_LOVES=?,LJ_STATUS=?)";
		try(
				Connection con = JDBCUtil.getConnection();
				PreparedStatement pstmt = con.prepareStatement(sql);
		){
			pstmt.setObject(1, entity.getLjId());
			pstmt.setObject(2, entity.getLjName());
			pstmt.setObject(3, entity.getLjNickname());
			pstmt.setObject(4, entity.getLjPassword());
			pstmt.setObject(5, entity.getLjSalt());
			pstmt.setObject(6, entity.getLjPhone());
			pstmt.setObject(7, entity.getLjQq());
			pstmt.setObject(8, entity.getLjSex());
			pstmt.setObject(9, entity.getLjBrithdate());
			pstmt.setObject(10, entity.getLjAddressCode());
			pstmt.setObject(11, entity.getLjAddress());
			pstmt.setObject(12, entity.getLjLoves());
			pstmt.setObject(13, entity.getLjStatus());
			
			return pstmt.executeUpdate();
		}
	}

	@Override
	public int deleteById(Serializable id) throws SQLException {
		String sql = "delete from lj where LJ_ID=?";
		try(
				Connection con = JDBCUtil.getConnection();
				PreparedStatement pstmt = con.prepareStatement(sql);	
		){
			pstmt.setObject(1, id);
			return pstmt.executeUpdate();
		}
	}

	@Override
	public Lj selectById(Serializable id) throws SQLException {
		String sql = "select * from lj where LJ_ID = ?";
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setObject(1, id);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				return load(rs);
			}else {
				throw new SQLException("没有对应记录！");
			}
		}finally {
			JDBCUtil.close(con,pstmt,rs);
		}
	}

	@Override
	public List<Lj> select(int index, int len) throws SQLException {
		String sql = "select * from lj order by LJ_ID limit ?,?";
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Lj> list = new ArrayList<Lj>();
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setObject(1, index);
			pstmt.setObject(2, len);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				list.add(load(rs));
			}
			return list;
		}finally {
			JDBCUtil.close(con,pstmt,rs);
		}
	}
	
	
	public List<User> select1(int index, int len) throws SQLException {
		String sql = "select * from sys_user order by USER_ID limit ?,?";
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<User> list = new ArrayList<User>();
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setObject(1, index);
			pstmt.setObject(2, len);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				list.add(load1(rs));
			}
			return list;
		}finally {
			JDBCUtil.close(con,pstmt,rs);
		}
	}

	@Override
	public long count() throws SQLException {
		String sql = "select count(*) as c from lj";
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				return rs.getLong(1);
			}
		}finally {
			JDBCUtil.close(con, pstmt, rs);
		}
		return 0;
	}

	public Lj load(ResultSet rs) throws SQLException{
		Lj entity = new Lj();
		entity.setLjId(rs.getLong("LJ_ID"));//基本数据类型的非空字段
		entity.setLjName(rs.getString("LJ_NAME"));
		entity.setLjNickname(rs.getString("LJ_NICKNAME"));
		entity.setLjPassword(rs.getString("LJ_PASSWORD"));
		entity.setLjSalt(rs.getString("LJ_SALT"));
		entity.setLjPhone(rs.getString("LJ_PHONE"));
		entity.setLjQq(rs.getString("LJ_QQ"));
		if(rs.getObject("LJ_SEX")!=null) {
			entity.setLjSex(rs.getBoolean("LJ_SEX"));
		}
		entity.setLjBrithdate(rs.getTimestamp("LJ_BRITHDATE"));
		entity.setLjAddressCode(rs.getString("LJ_ADDRESS_CODE"));
		entity.setLjAddress(rs.getString("LJ_ADDRESS"));
		entity.setLjLoves(rs.getString("LJ_LOVES"));
		entity.setLjStatus(rs.getInt("LJ_STATUS"));
		return entity;
	}
	
	public User load1(ResultSet rs) throws SQLException{
		User entity = new User();
		entity.setUserId(rs.getLong("USER_ID"));
		entity.setUserName(rs.getString("USER_NAME"));
		entity.setUserNickname(rs.getString("USER_NICKNAME"));
		entity.setUserPassword(rs.getString("USER_PASSWORD"));
		entity.setRoleId(rs.getInt("ROLE_ID"));
		//...
		entity.setUserStatus(rs.getInt("USER_STATUS"));
		entity.setUserSalt(rs.getString("USER_SALT"));

		return entity;
	}

	@Override
	public int update(Lj entity) throws SQLException {
		String sql = "update lj set LJ_NICKNAME=?,LJ_PHONE=?,LJ_QQ=?,LJ_SEX=?,LJ_BRITHDATE=? where LJ_ID=?";
		try(
				Connection con = JDBCUtil.getConnection();
				PreparedStatement pstmt = con.prepareStatement(sql);
		){
			pstmt.setObject(1, entity.getLjNickname());
			pstmt.setObject(2, entity.getLjPhone());
			pstmt.setObject(3, entity.getLjQq());
			pstmt.setObject(4, new Boolean(true).equals(entity.getLjSex())?1:0);
			pstmt.setObject(5, new SimpleDateFormat("yyyy-mm-dd").format(entity.getLjBrithdate()));
			pstmt.setObject(6, entity.getLjId());
			return pstmt.executeUpdate();
		}
	}

	@Override
	public Long count(String key) throws SQLException {
		
		return null;
	}

	@Override
	public User selectByModel(User user) throws SQLException{
		String sql = "select * from sys_user where USER_NAME ="+ "'"+user.getUserName()+"'";
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				return load1(rs);
			}else {
				throw new SQLException("没有对应记录！");
			}
		}finally {
			JDBCUtil.close(con,pstmt,rs);
		}
	}
	
	
	
	
	
	
	
	

}